﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.Enums;
using BCCommon.PaymentEnums;
using BCEntity.Common.Payment.WeChatPayBill.WxPayBillDetail;
using BCEntity.Common.Payment.WeChatPayBill.WxPayBillStatistics;
using Dapper;

namespace BCData.Common.Payment.WxPayBill
{
    public class WxPayBillData: IWxPayBillData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM WxPayBillStatistics WHERE TradeDate=?TradeDate";
        private const string QUERY_DETAILS = @"SELECT WxPayBillDetailId,TradeDate,TradeTime,WxOrderNo,PaymentRecordId,AppName,AppId,MchId,SpecificMchId,DeviceNo,WxUserNo,TradeType,
                                                    TradeTypeStr,TradeStatus,TradeStatusStr,PayerBank,PayerBankStr,CurrencyCode,CurrencyCodeStr,PaySingleAmount,CouponAmount,
                                                    RefundOrderNo,MchRefundOrderNo,RefundAmount,RechargeRefundAmount,RefundType,RefundStatus,GoodsName,MchDataPackage,HandlingFee,
                                                    FeeRate,FeeRateStr,FeeRateRemark,OrderAmount,ApplyRefundAmount,ActualReceiveAmount,CreateTime,ApplyRefundTime,RefundSuccessTime,IsSettle
                                               FROM WxPayBillDetail";
        private const string QUERY_STAT = @"SELECT WxPayBillStatisticsId,TradeDate,TradeOrderCount,PayOrderTotal,RefundTotal,RechargeRefundTotal,HandlingFeeTotal,OrderTotal,
                                                    ApplyRefundTotal,ApplyRefundCount,TradeSuccessCount,ActualReceiveTotal,CreateTime
                                            FROM WxPayBillStatistics";
        private const string QUERY_STAT_SUM = @"SELECT SUM(TradeOrderCount) AS TradeOrderCount,SUM(PayOrderTotal) AS PayOrderTotal,SUM(RefundTotal) AS RefundTotal,
                                                    SUM(RechargeRefundTotal) AS RechargeRefundTotal,SUM(HandlingFeeTotal) AS HandlingFeeTotal,SUM(OrderTotal) AS OrderTotal,
                                                    SUM(ApplyRefundTotal) AS ApplyRefundTotal,SUM(ApplyRefundCount) AS ApplyRefundCount,SUM(TradeSuccessCount) AS TradeSuccessCount,
                                                    SUM(ActualReceiveTotal) AS ActualReceiveTotal
                                            FROM WxPayBillStatistics";
        public WxPayBillData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public async Task<bool> SomeDayBillExists(DateTime tradeDate, WeChatBillType billType)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TradeDate", tradeDate.Date, DbType.Date);
            return await this.databaseContext.Slave.ExecuteScalarAsync(QUERY_EXISTS, parameters) !=null;
        }

        #region 添加微信对账单
        public async Task<bool> Insert(List<WxPayBillDetailEntity> detailEntities, WxPayBillStatisticsEntity statisticsEntity)
        {
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = SqlCommands.GetInsertSql("WxPayBillDetail", new string[] { "TradeDate","TradeTime","WxOrderNo","PaymentRecordId","AppName","AppId","MchId","SpecificMchId","DeviceNo","WxUserNo","TradeType",
                                                    "TradeTypeStr","TradeStatus","TradeStatusStr","PayerBank","PayerBankStr","CurrencyCode","CurrencyCodeStr","PaySingleAmount","CouponAmount",
                                                    "RefundOrderNo","MchRefundOrderNo","RefundAmount","RechargeRefundAmount","RefundType","RefundStatus","GoodsName","MchDataPackage","HandlingFee",
                                                    "FeeRate","FeeRateStr","FeeRateRemark","OrderAmount","ApplyRefundAmount","ActualReceiveAmount","CreateTime","ApplyRefundTime","RefundSuccessTime" });
            foreach (var item in detailEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?TradeDate", item.TradeDate, DbType.Date);
                parameters.Add("?TradeTime", item.TradeTime, DbType.DateTime);
                parameters.Add("?WxOrderNo", item.WxOrderNo, DbType.AnsiString, size: 50);
                parameters.Add("?PaymentRecordId", item.PaymentRecordId, DbType.Int64, size: 20);
                parameters.Add("?AppName", item.AppName, DbType.AnsiString, size: 50);
                parameters.Add("?AppId", item.AppId, DbType.AnsiString, size: 50);
                parameters.Add("?MchId", item.MchId, DbType.AnsiString, size: 30);
                parameters.Add("?SpecificMchId", item.SpecificMchId, DbType.AnsiString, size: 30);
                parameters.Add("?DeviceNo", item.DeviceNo, DbType.AnsiString, size: 50);
                parameters.Add("?WxUserNo", item.WxUserNo, DbType.AnsiString, size: 50);
                parameters.Add("?TradeType", item.TradeType, DbType.AnsiString, size: 20);
                parameters.Add("?TradeTypeStr", item.TradeTypeStr, DbType.AnsiString, size: 20);
                parameters.Add("?TradeStatus", item.TradeStatus, DbType.AnsiString, size: 20);
                parameters.Add("?TradeStatusStr", item.TradeStatusStr, DbType.AnsiString, size: 20);
                parameters.Add("?PayerBank", item.PayerBank, DbType.AnsiString, size: 30);
                parameters.Add("?PayerBankStr", item.PayerBankStr, DbType.AnsiString, size: 30);
                parameters.Add("?CurrencyCode", item.CurrencyCode, DbType.AnsiString, size: 20);
                parameters.Add("?CurrencyCodeStr", item.CurrencyCodeStr, DbType.AnsiString, size: 20);
                parameters.Add("?PaySingleAmount", item.PaySingleAmount, DbType.Decimal);
                parameters.Add("?CouponAmount", item.CouponAmount, DbType.Decimal);
                parameters.Add("?RefundOrderNo", item.RefundOrderNo, DbType.AnsiString, size: 50);
                parameters.Add("?MchRefundOrderNo", item.MchRefundOrderNo, DbType.AnsiString, size: 50);
                parameters.Add("?RefundAmount", item.RefundAmount, DbType.Decimal);
                parameters.Add("?RechargeRefundAmount", item.RechargeRefundAmount, DbType.Decimal);
                parameters.Add("?RefundType", item.RefundType, DbType.AnsiString, size: 30);
                parameters.Add("?RefundStatus", item.RefundStatus, DbType.AnsiString, size: 30);
                parameters.Add("?GoodsName", item.GoodsName, DbType.AnsiString, size: 200);
                parameters.Add("?MchDataPackage", item.MchDataPackage, DbType.AnsiString, size: 200);
                parameters.Add("?HandlingFee", item.HandlingFee, DbType.Decimal);
                parameters.Add("?FeeRate", item.FeeRate, DbType.Int32, size: 10);
                parameters.Add("?FeeRateStr", item.FeeRateStr, DbType.AnsiString, size: 20);
                parameters.Add("?FeeRateRemark", item.FeeRateRemark, DbType.String);
                parameters.Add("?OrderAmount", item.OrderAmount, DbType.Decimal);
                parameters.Add("?ApplyRefundAmount", item.ApplyRefundAmount, DbType.Decimal);
                parameters.Add("?ActualReceiveAmount", item.ActualReceiveAmount, DbType.Decimal);
                parameters.Add("?CreateTime", item.CreateTime, DbType.DateTime);
                parameters.Add("?ApplyRefundTime", item.ApplyRefundTime, DbType.DateTime);
                parameters.Add("?RefundSuccessTime", item.RefundSuccessTime, DbType.DateTime);
                dynamicParameters.Add(parameters);
            }
            var result = await this.databaseContext.Master.ExecuteAsync(sql, dynamicParameters, databaseContext.Transaction) > 0;
            if (!result)
            {
                throw new ArgumentException("插入微信支付账单记录失败!");
            }
            dynamicParameters = new List<DynamicParameters>();
            sql = SqlCommands.GetInsertSql("WxPayBillStatistics", new string[] { "TradeDate","TradeType","TradeTypeStr","TradeOrderCount",
                                                    "PayOrderTotal","RefundTotal","RechargeRefundTotal","HandlingFeeTotal","OrderTotal",
                                                    "ApplyRefundTotal","ApplyRefundCount","TradeSuccessCount","ActualReceiveTotal","CreateTime" });
            DynamicParameters statParameters = new DynamicParameters();
            statParameters.Add("?TradeDate", statisticsEntity.TradeDate, DbType.Date);
            statParameters.Add("?TradeType", statisticsEntity.TradeType, DbType.AnsiString, size: 20);
            statParameters.Add("?TradeTypeStr", statisticsEntity.TradeTypeStr, DbType.AnsiString, size: 20);
            statParameters.Add("?TradeOrderCount", statisticsEntity.TradeOrderCount, DbType.Int32, size: 10);
            statParameters.Add("?PayOrderTotal", statisticsEntity.PayOrderTotal, DbType.Decimal);
            statParameters.Add("?RefundTotal", statisticsEntity.RefundTotal, DbType.Decimal);
            statParameters.Add("?RechargeRefundTotal", statisticsEntity.RechargeRefundTotal, DbType.Decimal);
            statParameters.Add("?HandlingFeeTotal", statisticsEntity.HandlingFeeTotal, DbType.Decimal);
            statParameters.Add("?OrderTotal", statisticsEntity.OrderTotal, DbType.Decimal);
            statParameters.Add("?ApplyRefundTotal", statisticsEntity.ApplyRefundTotal, DbType.Decimal);
            statParameters.Add("?ApplyRefundCount", statisticsEntity.ApplyRefundCount, DbType.Int32);
            statParameters.Add("?ActualReceiveTotal", statisticsEntity.ActualReceiveTotal, DbType.Decimal);
            statParameters.Add("?TradeSuccessCount", statisticsEntity.TradeSuccessCount, DbType.Int32);
            statParameters.Add("?CreateTime", statisticsEntity.CreateTime, DbType.DateTime);
            dynamicParameters.Add(statParameters);
            return await this.databaseContext.Master.ExecuteAsync(sql, dynamicParameters, databaseContext.Transaction) >0;
        }
        #endregion

        public async Task<(IEnumerable<WxPayBillDetailEntity>, WxPayBillStatisticsEntity)> GetSomeDayBill(DateTime tradeDate, WeChatBillType weChatBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("TradeDate=?TradeDate");
            parameters.Add("?TradeDate", tradeDate.Date, DbType.Date);
            if(weChatBillType != WeChatBillType.ALL)
            {
                wheres.Add("TradeType=?TradeType");
                parameters.Add("?TradeType", weChatBillType.ToString(), DbType.AnsiString);
            }

            string sql = string.Format("{0} {2} ORDER BY TradeTime DESC; {1} {2}", QUERY_DETAILS, QUERY_STAT, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            
            return await this.databaseContext.Slave.QueryMultipleAsync<WxPayBillDetailEntity, WxPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<WxPayBillDetailEntity>, WxPayBillStatisticsEntity)> GetSomeMonthBill(int year, int month, WeChatBillType weChatBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("DATE_FORMAT(TradeDate,'%Y-%m')=DATE_FORMAT(STR_TO_DATE(?TradeDate,'%Y-%m-%d'),'%Y-%m')");
            parameters.Add("?TradeDate", new DateTime(year, month, 1), DbType.Date);
            if (weChatBillType != WeChatBillType.ALL)
            {
                wheres.Add("TradeType=?TradeType");
                parameters.Add("?TradeType", weChatBillType.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {2} ORDER BY TradeTime DESC; {1} {2}", 
                QUERY_DETAILS,
                QUERY_STAT_SUM, 
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.databaseContext.Slave.QueryMultipleAsync<WxPayBillDetailEntity, WxPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<WxPayBillDetailEntity>, WxPayBillStatisticsEntity)> GetSomeYearBill(int year, WeChatBillType weChatBillType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("DATE_FORMAT(TradeDate,'%Y')=?Year");
            parameters.Add("?Year", year, DbType.Int32);
            if (weChatBillType != WeChatBillType.ALL)
            {
                wheres.Add("TradeType=?TradeType");
                parameters.Add("?TradeType", weChatBillType.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {2} ORDER BY TradeTime DESC; {1} {2}",
                QUERY_DETAILS,
                QUERY_STAT_SUM,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "");
            return await this.databaseContext.Slave.QueryMultipleAsync<WxPayBillDetailEntity, WxPayBillStatisticsEntity>(sql, parameters);
        }

        public async Task<WxPayBillDetailEntity> GetEntityByPaymentRecordId(long paymentRecordId)
        {
            string sql = string.Format("{0} WHERE PaymentRecordId=?PaymentRecordId ORDER BY CreateTime DESC LIMIT 1", QUERY_DETAILS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?PaymentRecordId", paymentRecordId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<WxPayBillDetailEntity>(sql, parameters);
        }
    }
}
